package org.hp.dao;

import org.hp.util.DBHelper;
import org.hp.util.PageBean;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Dao {
    //    查询
    public List<Map> select(String sql, Object... obj) {
        Connection conn = DBHelper.getCon();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
//            System.out.println("查询方法sql:" + sql);
            for (int i = 0; i < obj.length; i++) {
                Object o = obj[i];
//                System.out.println("第" + (i + 1) + "个参数：" + o);
                ps.setObject(i + 1, o);
            }
            rs = ps.executeQuery();
            ResultSetMetaData md = rs.getMetaData();
            List<Map> list = new ArrayList<>();
            while (rs.next()) {
                Map map = new HashMap();
                int index = md.getColumnCount();//获取列数
                for (int i = 1; i <= index; i++) {
                    String key = md.getColumnLabel(i);
                    map.put(key, rs.getObject(key));
                }
                list.add(map);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.close();
        }
        return null;
    }

    /**
     * @param id 唯一主键
     */
    public Map findById(String sql, Object id) {
        List<Map> list = select(sql, id);
        return list != null && list.size() > 0 ? list.get(0) : null;
//        if(list!=null&&list.size()>0){
//          return list.get(0);
//        }else{
//          return null;
//        }
    }

    //    修改,删除，添加
    public int update(String sql, Object... obj) {
        int i = 0;
        Connection conn = DBHelper.getCon();
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
//            System.out.println("修改,删除，添加sql:"+sql);
            for (int j = 0; j < obj.length; j++) {
                Object o = obj[j];
//                System.out.println("第" + (j + 1) + "个参数：" + o);
                ps.setObject(j + 1, o);
            }
            i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBHelper.close();
        }
        return i;
    }

    public int getCount(String sql, String... obj) {
        List<Map> list = select("select count(*) num from(" + sql + ") d", obj);
        return list != null && list.size() > 0 ? Integer.valueOf(list.get(0).get("num").toString()) : 0;
    }

    public PageBean<Map> pageList(int page, int limit ,String sql, String...obj) {
//        1.获取总条数
        int count = getCount(sql, obj);
//            2.根据总条数和每页条数进行计算总页数(根据是否有余数，判断是否需要页数加一)
        int totlePages = count%limit==0?count/limit:(count/limit)+1;
//            3.判断当前页是否大于总页数或小于1页
        if(page<=1){
            page=1;
        }else if(page>totlePages){
            page=totlePages;
        }
//      4.判断通过后计算当前分页的起始位置
        int startindex=(page-1)*limit;
//        System.out.println("Dao层page的值："+startindex);
        sql +=" limit "+startindex+","+limit;
        List<Map> list = select(sql, obj);
        return new PageBean<Map>(totlePages, count, page, limit, list);
    }

}
